package control;

import Entity.goodBean;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class goodsControl {
    public goodsControl() {
    }
    //获取当前的商品列表
    public static void get_Goods(HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            Connection con=null;
            Statement stmt1=null,stmt2=null;
            ResultSet rs=null,rs2=null;
            Class.forName("org.h2.Driver");
            /*url后面加的?useUnicode=true&characterEncoding=gbk，是为了处理向数据库中添加数据时出现乱码的问题。*/
            String url="jdbc:h2:~/test1";
            //System.out.println("到达");
            con= DriverManager.getConnection(url,"root","root");
            //System.out.println("到达2");
            HttpSession session=request.getSession();
            session.removeAttribute("goodBean");
            stmt1=con.createStatement();
            stmt2=con.createStatement();
            String sql="select * from goods";
            rs=stmt1.executeQuery(sql);
            ArrayList goodslist=null;
            if((ArrayList)session.getAttribute("goodslist")==null){
                goodslist=new ArrayList();
                while(rs.next()){
                    goodBean ff = new goodBean();
                    ff.setType(rs.getString("type"));
                    ff.setGoods_id(rs.getString("goods_id"));
                    ff.setName(rs.getString("name"));
                    ff.setGoods_stock(rs.getString("goods_stock"));
                    ff.setGoods_sell(rs.getString("goods_sell"));
                    ff.setGoods_factory(rs.getString("goods_factory"));
                    ff.setGoods_price(rs.getString("goods_price"));
                    ff.setGoods_read(rs.getString("goods_read"));
                    ff.setGoods_img(rs.getString("goods_img"));
                    ff.setGoods_time(rs.getString("goods_time"));
                    String sql2="select * from good_type where type_id = "+ rs.getString("type_id");
                    rs2=stmt2.executeQuery(sql2);
                    while(rs2.next()) {
                        ff.setBid_type(rs2.getString("type_name"));
                    }
                    System.out.println(ff);
                    goodslist.add(ff);
                }
                session.setAttribute("goodslist", goodslist);
            }
            rs.close();
            stmt1.close();
            stmt2.close();
            con.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        return;
    }
    //将商品信息导入数据库中
    public static void set_Goods(goodBean ff,HttpServletRequest request, HttpServletResponse response)
    {
        try{
            Connection con=null;
            Statement stmt=null;
            Class.forName("org.h2.Driver");
            /*url后面加的?useUnicode=true&characterEncoding=gbk，是为了处理向数据库中添加数据时出现乱码的问题。*/
            String url="jdbc:h2:~/test1";
            //System.out.println("到达");
            con= DriverManager.getConnection(url,"root","root");
            stmt=con.createStatement();
            ResultSet rs=null;
            //System.out.println("导入商品数据库到达2");
            //获取当前数据库中商品的数量
            String sql="select * from goods";
            rs=stmt.executeQuery(sql);
            rs.last();
            //获取商品id的最大值并+1
            String numss = null;
            if (rs.getRow()!=0) {
                String goods_id=rs.getString("goods_id");
                long num3 = Long.valueOf(goods_id);
                numss = String.valueOf(num3+1);
            }
            //获取当前数据库中商品分类的数量
            String sql3="select * from good_type";
            rs=stmt.executeQuery(sql3);
            String num2 = null;
            int flag = 1;
            //查看商品分类数据库中是否已经存在
            int count = 0;
            while(rs.next()) {
                if(rs.getString("type_name").equals(ff.getBid_type()))
                {
                    if(rs.getString("type_id") != null) {
                        //System.out.println(rs.getString("type_id"));
                        String nums = rs.getString("type_id");
                        num2 = nums;
                        flag = 0;
                    }
                }
                count++;
            }
            if(flag == 1) {
                num2 = String.valueOf(11000 + count);
            }

            //插入商品到数据库中
            //查找商品数据库中是否存在
            String sql7="select * from goods where name='"+ff.getName()+"'";
            rs=stmt.executeQuery(sql7);
            String num1 = null;
            rs.last();
            if(rs.getRow()!=0) {
                System.out.println("删除成功！"+rs.getRow());
                num1 = rs.getString("goods_id");
                String sql6="DELETE FROM goods WHERE name='"+ff.getName()+"';";
                stmt.executeUpdate(sql6);
            }
            else{
                num1 = numss;
                System.out.println(num1);
            }
            String sql2 = new String();
            System.out.println(ff.getGoods_img());
            sql2="insert into goods"+"(type,goods_id,name,goods_stock,goods_sell,goods_factory,goods_price,goods_read,goods_img,goods_time,type_id)"+"values("+"'"+ff.getType()+"',"+"'"+num1+"',"+"'"+ff.getName()+"',"+"'"+ff.getGoods_stock()+"',"+"'"+ff.getGoods_sell()+"',"+"'"+ff.getGoods_factory()+"',"+"'"+ff.getGoods_price()+"',"+"'"+ff.getGoods_read()+"',"+"'"+ff.getGoods_img()+"',"+"'"+ff.getGoods_time()+"',"+"'"+num2+"')";
            System.out.println(sql2);
            stmt.execute(sql2);

            //插入商品分类到数据库中
            if(flag == 1) {
                String sql4 = "insert into good_type" + "(type_id,type_name)" + "values(" + "'" + num2 + "'," + "'" + ff.getBid_type() + "')";
                stmt.executeUpdate(sql4);
            }
            stmt.close();
            con.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        return;
    }
}
